﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using YXShop.IDAL.Templates;
using System.Data.SqlClient;
using System.Data;

namespace YXShop.SQLServerDAL.Templates
{
    public class LabelType:DbBase, ILabelType
    {
        #region  成员方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(YXShop.Model.Templates.LabelType model)
        {
            string sequel = "Insert into ["+Pre+"labeltype](";
            sequel = sequel + "[name], [description], [creattime],[isstorelabel],[styleid])";
            sequel = sequel + "Values(";
            sequel = sequel + "@name, @description,@creattime,@isstorelabel,@styleid) Select scope_IDENTITY() ";
            SqlParameter[] paras = (SqlParameter[])this.ValueParas(model);
            object obj = ChangeHope.DataBase.SQLServerHelper.ExecuteSql(sequel, paras);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(YXShop.Model.Templates.LabelType model)
        {
            string sequel = "Update [" + Pre + "labeltype] set  ";
            sequel = sequel + "[name] =@name ,[description] =@description ,[creattime]=@creattime,[isstorelabel]=@isstorelabel,[styleid]=@styleid";
            sequel = sequel + UpdateWhereSequel;
            object obj = ChangeHope.DataBase.SQLServerHelper.ExecuteSql(sequel, (SqlParameter[])this.ValueParas(model));
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int id)
        {
            string sequel = string.Empty;
            sequel = "Delete From [" + Pre + "labeltype]" + this.UpdateWhereSequel;
            SqlParameter[] parameters = (SqlParameter[])this.IDValueParas(id);
            object obj = ChangeHope.DataBase.SQLServerHelper.ExecuteSql(sequel, parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public YXShop.Model.Templates.LabelType GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,name,description,creattime,isstorelabel,styleid from " + Pre + "labeltype ");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = (SqlParameter[])this.IDValueParas(id);

            YXShop.Model.Templates.LabelType model = new YXShop.Model.Templates.LabelType();
            DataSet ds = ChangeHope.DataBase.SQLServerHelper.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                model.Name = ds.Tables[0].Rows[0]["name"].ToString();
                model.Description = ds.Tables[0].Rows[0]["description"].ToString();
                if (ds.Tables[0].Rows[0]["creattime"].ToString() != "")
                {
                    model.CreatTime = DateTime.Parse(ds.Tables[0].Rows[0]["creattime"].ToString());
                }
                model.IsStoreLabel =int.Parse(ds.Tables[0].Rows[0]["isstorelabel"].ToString());
                if (ds.Tables[0].Rows[0]["styleid"].ToString() != "")
                {
                    model.StyleId = int.Parse(ds.Tables[0].Rows[0]["styleid"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到集合
        /// </summary>
        /// <returns></returns>
        public List<YXShop.Model.Templates.LabelType> GetAll()
        {
            List<YXShop.Model.Templates.LabelType> list = new List<YXShop.Model.Templates.LabelType>();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select [id],[name],[description],[creattime],[isstorelabel],[styleid] from " + Pre + "labeltype ");
            using (SqlDataReader reader = ChangeHope.DataBase.SQLServerHelper.ExecuteReader(strSql.ToString()))
            {
                while (reader.Read())
                {
                    YXShop.Model.Templates.LabelType model = new YXShop.Model.Templates.LabelType();
                    model.ID = reader.GetInt32(0);
                    model.Name = reader.GetString(1);
                    model.Description = reader.GetString(2);
                    model.CreatTime = reader.GetDateTime(3);
                    model.IsStoreLabel = reader.GetInt32(4);
                    model.StyleId = reader.GetInt32(5);
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <remarks></remarks>
        public ChangeHope.DataBase.DataByPage GetList()
        {
            ChangeHope.DataBase.DataByPage dataPage = new ChangeHope.DataBase.DataByPage();
            dataPage.Sql = "[select] [id], [name], [description], [creattime], [isstorelabel], [styleid],(select name from " + Pre + "shopstyle where " + Pre + "labeltype.styleid=" + Pre + "shopstyle.id) as StyleName [from] " + Pre + "labeltype [where] 1=1 [order by] id asc";
            dataPage.GetRecordSetByPage();
            return dataPage;
        }

        #endregion  成员方法

        #region "Other function"
        string selectSequel = string.Empty;
        /// <summary>
        /// 该数据访问对象从数据库中提取数据的Sql语句 
        /// </summary>
        /// <remarks></remarks>
        protected string SelectSequel
        {
            get
            {
                if (selectSequel == string.Empty)
                    selectSequel = "SELECT [id], [name], [description], [creattime],[isstorelabel],[styleid] FROM [" + Pre + "labeltype]";
                return selectSequel;
            }
            set
            {
                this.selectSequel = value;
            }
        }
        protected string UpdateWhereSequel
        {
            get
            {
                return " Where [id] = @id";
            }
        }
        /// <summary>
        /// 该数据访问对象的属性值装载到数据库更新参数数组
        /// </summary>
        /// <remarks></remarks>
        private SqlParameter[] ValueParas(YXShop.Model.Templates.LabelType model)
        {
            SqlParameter[] paras = new SqlParameter[6];
            paras[0] = new SqlParameter("@id", SqlDbType.Int, 4);
            paras[0].Value = model.ID;
            paras[1] = new SqlParameter("@name", SqlDbType.VarChar, 20);
            paras[1].Value = model.Name;
            paras[2] = new SqlParameter("@description", SqlDbType.VarChar, 50);
            paras[2].Value = model.Description;
            paras[3] = new SqlParameter("@creattime", SqlDbType.DateTime, 4);
            paras[3].Value = model.CreatTime;
            paras[4] = new SqlParameter("@isstorelabel",SqlDbType.Int,4);
            paras[4].Value = model.IsStoreLabel;
            paras[5] = new SqlParameter("@styleid", SqlDbType.Int, 4);
            paras[5].Value = model.StyleId;
            return paras;


        }
        private SqlParameter[] IDValueParas(int id)
        {
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@id", SqlDbType.Int, 4);
            paras[0].Value =id;
            return paras;
        }
        #endregion
    }
}
